package com.cuiweiyou.uiautomator20;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * <b>类名</b>: UtilSQLite.java，数据库工具 <br/>
 * <b>说明</b>: 存储收藏文档 www.gaohaiyan.com<br/>
 * <b>创建</b>: 2016-2016年6月23日_下午12:53:16 <br/>
 *
 * @author cuiweiyou.com
 */
public class UtilSQLite extends SQLiteOpenHelper {

    private final String NAME_TB_CHAT = "tb_chat";
    private final String NAME_TB_SEARCH = "tb_search";
    private final String NAME_TB_FRIEND = "tb_friend";

    private static UtilSQLite instance;
    private static String txtSearchFilePath;
    private static String txtChatFilePath;
    private static String txtListFilePath;

    public static UtilSQLite getInstance(Context context) {
        if (null == instance) {
            synchronized (UtilSQLite.class) {
                if (null == instance) {
                    UtilLogs.saveLog("SQLITE_ 创建工具类");

                    File pathFile = new File(Constant.SOURCEPATH);

                    String dbFilePath = pathFile.getAbsolutePath() + "/friend.db";
                    txtSearchFilePath = pathFile.getAbsolutePath() + "/friendSearch.txt";
                    txtChatFilePath = pathFile.getAbsolutePath() + "/friendChat.txt";
                    txtListFilePath = pathFile.getAbsolutePath() + "/friendList.txt";

                    UtilLogs.saveLog("自定义db路径：" + dbFilePath);

                    File dbFile = new File(dbFilePath);
                    if (!dbFile.exists()) {
                        try {
                            dbFile.createNewFile();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    File txtSearchFile = new File(txtSearchFilePath);
                    if (!txtSearchFile.exists()) {
                        try {
                            txtSearchFile.createNewFile();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    File txtChatFile = new File(txtChatFilePath);
                    if (!txtChatFile.exists()) {
                        try {
                            txtChatFile.createNewFile();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    File txtListFile = new File(txtListFilePath);
                    if (!txtListFile.exists()) {
                        try {
                            txtListFile.createNewFile();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }

                    instance = new UtilSQLite(context, dbFilePath, null, 1);
                }
            }
        }
        return instance;
    }

    private UtilSQLite(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        UtilLogs.saveLog("SQLITE_ 初始化数据库：" + db.getPath());

        // 如果表数据将来用于SimpleCursorAdapter，注意其只识别带下划线的主键。 _id 主键
        db.execSQL("CREATE TABLE " + NAME_TB_SEARCH + " (" + //
                           "_id INTEGER PRIMARY KEY, " + //
                           "phone VARCHAR, " +    // 查找的电话号码
                           "wxid VARCHAR, " +     // 微信号
                           "notename VARCHAR, " + // 备注名
                           "nickname VARCHAR, " + // 昵称
                           "timestamp VARCHAR, " + // 查找时间戳
                           "datetime VARCHAR, " +  // 查找时间年月日
                           "note VARCHAR " +      // 查找结果：yes-请求了加好友/none-未找到
                           ");");

        db.execSQL("CREATE TABLE " + NAME_TB_FRIEND + " (" + //
                           "_id INTEGER PRIMARY KEY, " +   //
                           "phone VARCHAR, " +             // 电话
                           "wxid VARCHAR, " +              // 微信号
                           "notename VARCHAR, " +          // 备注名
                           "nickname VARCHAR, " +          // 昵称
                           "timestamp VARCHAR, " +         // 互为好友时间戳
                           "datetime VARCHAR, " +          // 互为好友时间年月日
                           "type VARCHAR, " +              // 互为好友方式：reactive对方请求的，proactive我主动查找申请好友的
                           "isothersidedeleted INTEGER " + // 对方是否已经删除了好友关系1-删了，0-没
                           ");");

        db.execSQL("CREATE TABLE " + NAME_TB_CHAT + " (" + //
                           "_id INTEGER PRIMARY KEY, " + //
                           "firend VARCHAR, " +      // 好友的电话
                           "wxid VARCHAR, " +        // 好友的微信号
                           "message VARCHAR, " +     // 对方聊天内容
                           "timestamp VARCHAR, " +   // 聊天时间戳
                           "datetime VARCHAR, " +    // 聊天时间年月日
                           "role VARCHAR " +         // me-我、other-对方
                           ");");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    }

    /**
     * 保存查找记录
     *
     * @param phone    目标号码
     * @param wxid     微信号
     * @param notename 备注名
     * @param nickname 昵称
     * @param note     yes-发送的加好友请求，none-没找到
     */
    public void insertSearch(String phone, String wxid, String notename, String nickname, String note) {

        UtilLogs.saveLog("保存查找记录：" + phone);

        SQLiteDatabase wdb = getWritableDatabase();    // 获取可写的数据库
        // wdb.execSQL("insert into "+NAME_TB+" (grouper, title, url) values ('" + bean.getGroup() + "', '" + bean.getTitle() + "', '" + bean.getUrl() + "')");  // 插入

        ContentValues cv = new ContentValues();
        cv.put("phone", phone);
        cv.put("wxid", wxid);         // 微信号
        cv.put("notename", notename); // 备注名
        cv.put("nickname", nickname); // 昵称
        long timeMillis = System.currentTimeMillis();
        String yymmddhHmmss = UtilDate.getNowYYMMDDHHmmss(timeMillis);
        cv.put("timestamp", timeMillis + "");
        cv.put("datetime", yymmddhHmmss);
        cv.put("note", note);
        long nums = wdb.insert(NAME_TB_SEARCH, null, cv);

        wdb.close();

        String msg = phone + "\t" + wxid + "\t" + notename + "\t" + nickname + "\t" + timeMillis + "\t" + yymmddhHmmss + "\t" + note + "\n";
        UtilFile.appendText(txtSearchFilePath, msg);

        UtilLogs.saveLog("SQLITE_ 执行'加好友查找'插入：" + nums);
    }

    /**
     * 存储好友
     *
     * @param phone    电话
     * @param wxid     微信号
     * @param notename 备注名
     * @param nickname 昵称
     * @param type     来源：reactive对方请求的，proactive我主动查找申请好友的
     * @ p a ram isothersidedeleted     对方是否删除了好友关系，默认是false-0
     */
    public void insertFriend(String phone, String wxid, String notename, String nickname, String type) {
        UtilLogs.saveLog("插入新的好友");

        ModelFriend isHave = hasFriend(phone, wxid, notename, nickname, type); // sqlite保存新朋友前检查
        if (null != isHave) {
            UtilLogs.saveLog("插入新的好友，已经有了");
            return;
        }

        UtilLogs.saveLog("保存好友，电话：" + phone + "，微信号：" + wxid + "，备注名：" + notename + "，昵称：" + nickname + "，来源：" + type);

        SQLiteDatabase wdb = getWritableDatabase();    // 获取可写的数据库
        // wdb.execSQL("insert into "+NAME_TB+" (grouper, title, url) values ('" + bean.getGroup() + "', '" + bean.getTitle() + "', '" + bean.getUrl() + "')");  // 插入

        ContentValues cv = new ContentValues();
        cv.put("phone", phone);
        cv.put("wxid", wxid);
        cv.put("notename", notename);
        cv.put("nickname", nickname);
        long timeMillis = System.currentTimeMillis();
        String yymmddhHmmss = UtilDate.getNowYYMMDDHHmmss(timeMillis);
        cv.put("timestamp", timeMillis + "");
        cv.put("datetime", yymmddhHmmss);
        cv.put("type", type);
        cv.put("isothersidedeleted", 0);
        long nums = wdb.insert(NAME_TB_FRIEND, null, cv);

        wdb.close();

        String msg = phone + "\t" + wxid + "\t" + notename + "\t" + nickname + "\t" + timeMillis + "\t" + yymmddhHmmss + "\t" + type + "\t" + 0 + "\n";
        UtilFile.appendText(txtListFilePath, msg);

        UtilLogs.saveLog("SQLITE_ 执行好友联系人插入：" + nums);
    }

    /**
     * 存储聊天
     *
     * @param friend  好友的电话
     * @param wxid    好友的微信号
     * @param message 对方聊天内容
     * @param role    谁的发言：me-我、other-对方
     */
    public void insertChat(String friend, String wxid, String message, String role) {

        UtilLogs.saveLog("保存聊天，电话：" + friend + "，微信号：" + wxid + "，消息：" + message + "，谁：" + role);

        SQLiteDatabase wdb = getWritableDatabase();    // 获取可写的数据库
        // wdb.execSQL("insert into "+NAME_TB+" (grouper, title, url) values ('" + bean.getGroup() + "', '" + bean.getTitle() + "', '" + bean.getUrl() + "')");  // 插入

        ContentValues cv = new ContentValues();
        cv.put("firend", friend);
        cv.put("wxid", wxid);
        cv.put("message", message);
        long timeMillis = System.currentTimeMillis();
        String yymmddhHmmss = UtilDate.getNowYYMMDDHHmmss(timeMillis);
        cv.put("timestamp", timeMillis + "");
        cv.put("datetime", yymmddhHmmss);
        cv.put("role", role);
        long nums = wdb.insert(NAME_TB_CHAT, null, cv);

        wdb.close();

        String msg = friend + "\t" + wxid + "\t" + message + "\t" + timeMillis + "\t" + yymmddhHmmss + "\t" + role + "\n";
        UtilFile.appendText(txtChatFilePath, msg);

        UtilLogs.saveLog("SQLITE_ 执行聊天插入：" + nums);
    }

    public void deleteFriend(ModelFriend bean) {

        //SQLiteDatabase wdb = sqLiteOpenHelper.getWritableDatabase();
        // wdb.execSQL("delete from tb where title = '" + bean.getTitle() + "'");
        //int delete = wdb.delete(NAME_TB, "wxid = ?", new String[]{bean.getWxid()});

        //wdb.close();
        //Log.e("ard", "SQLITE_ 执行删除：" + delete);
    }


    public ModelFriend hasFriend(String phone) {
        List<ModelFriend> friendList = getAllFriend(); // 添加好友前
        for (int i = 0; i < friendList.size(); i++) {
            ModelFriend fb = friendList.get(i);
            String fbPhone = fb.getPhone();
            String fbWxid = fb.getWxid();
            boolean phoneMate = !TextUtils.isEmpty(fbPhone) && fbPhone.trim().equals(phone.trim());
            boolean wxidMate = !TextUtils.isEmpty(fbWxid) && fbWxid.trim().equals(phone.trim());
            if (phoneMate || wxidMate) {
                return fb;
            }
        }

        return null;
    }

    /**
     * @param phone
     * @param wxid
     * @param notename
     * @param nickname
     * @param type     来源：reactive对方请求的，proactive我主动查找申请好友的
     * @return
     */
    public ModelFriend hasFriend(String phone, String wxid, String notename, String nickname, String type) {
        List<ModelFriend> friendList = getAllFriend();
        for (int i = 0; i < friendList.size(); i++) {
            ModelFriend friend = friendList.get(i);
            UtilLogs.saveLog("好友：" + friend);

            phone = phone.trim();
            wxid = wxid.trim();
            notename = notename.trim();
            nickname = nickname.trim();

            String wxid111 = friend.getWxid().trim();
            String phone111 = friend.getPhone().trim();
            String notename111 = friend.getNotename().trim();
            String nickname111 = friend.getNickname().trim();
            if (!TextUtils.isEmpty(phone) && !TextUtils.isEmpty(phone111)) {
                if (phone.equals(phone111)) {
                    UtilLogs.saveLog("phone已经存在的好友：" + friend.toString());
                    updateFriend(friend, phone, wxid, notename, nickname);//phone已经存在的好友
                    return friend;
                }
            }
            if (!TextUtils.isEmpty(wxid) && !TextUtils.isEmpty(wxid111)) {
                if (wxid.equals(wxid111)) {
                    UtilLogs.saveLog("wxid已经存在的好友：" + friend.toString());
                    updateFriend(friend, phone, wxid, notename, nickname);//wxid已经存在的好友
                    return friend;
                }
            }
            if (!TextUtils.isEmpty(notename) && !TextUtils.isEmpty(notename111)) {
                if (notename.equals(notename111)) {
                    UtilLogs.saveLog("notename已经存在的好友：" + friend.toString());
                    updateFriend(friend, phone, wxid, notename, nickname);//notename已经存在的好友
                    return friend;
                }
            }
            if (!TextUtils.isEmpty(nickname) && !TextUtils.isEmpty(nickname111)) {
                if (nickname.equals(nickname111)) {
                    UtilLogs.saveLog("nickname已经存在的好友：" + friend.toString());
                    updateFriend(friend, phone, wxid, notename, nickname);//nickname已经存在的好友
                    return friend;
                }
            }
        }

        return null;
    }

    private void updateFriend(ModelFriend friend, String phone, String wxid, String notename, String nickname) {
        UtilLogs.saveLog("更新phone：" + phone + "，wxid：" + wxid + "，notename：" + notename + "，nickname：" + nickname);

        ContentValues cv = new ContentValues();
        if (TextUtils.isEmpty(friend.getPhone()) && !TextUtils.isEmpty(phone)) {
            cv.put("phone", phone);
            friend.setPhone(phone);
        }
        if (TextUtils.isEmpty(friend.getWxid()) && !TextUtils.isEmpty(wxid)) {
            cv.put("wxid", wxid);
            friend.setWxid(wxid);
        }
        if (TextUtils.isEmpty(friend.getNotename()) && !TextUtils.isEmpty(notename)) {
            cv.put("notename", notename);
            friend.setNotename(notename);
        }
        if (TextUtils.isEmpty(friend.getNickname()) && !TextUtils.isEmpty(nickname)) {
            cv.put("nickname", nickname);
            friend.setNickname(nickname);
        }
        boolean b = cv.keySet().iterator().hasNext();
        if (b) {
            SQLiteDatabase db = getWritableDatabase();

            int count = db.update(NAME_TB_FRIEND, cv, "_id = ?", new String[]{friend.getDbIndex()});
            db.close();
            UtilLogs.saveLog("更新行数：" + count + "，好友：" + friend.toString());
            return;
        }

        UtilLogs.saveLog("更新行数=0" + "，好友：" + friend.toString());
    }

    public void setFriendIsDeleted(ModelFriend friend, boolean isothersidedeleted) {
        ContentValues cv = new ContentValues();
        cv.put("isothersidedeleted", (isothersidedeleted ? 1 : 0));
        SQLiteDatabase db = getWritableDatabase();

        int count = db.update(NAME_TB_FRIEND, cv, "_id = ?", new String[]{friend.getDbIndex()});
        db.close();

        UtilLogs.saveLog("更新朋友关系行数：" + count + "，好友：" + friend.toString());
    }

    public long getCount() {
        long count = 0;

        //SQLiteDatabase rdb = sqLiteOpenHelper.getReadableDatabase(); // 获取只读类型的数据库
        //Cursor cursor = rdb.rawQuery("select count(*) count from " + NAME_TB, null);// sql查询
        //int i = cursor.getColumnIndex("count");
        //count = cursor.getLong(i);
        //
        //cursor.close();
        //rdb.close();
        //
        //Log.e("ard", "SQLITE_ 获取总量：" + count);

        return count;
    }

    /**
     * 查找全部的朋友
     *
     * @return
     */
    public List<ModelFriend> getAllFriend() {

        List<ModelFriend> list = new ArrayList<ModelFriend>();

        SQLiteDatabase rdb = getReadableDatabase();    // 获取只读类型的数据库
        // sql查询
        Cursor cursor = rdb.rawQuery("select * from " + NAME_TB_FRIEND, null);
        while (cursor.moveToNext()) {
            int _idIndex = cursor.getColumnIndex("_id");
            int phoneIndex = cursor.getColumnIndex("phone");
            int wxidIndex = cursor.getColumnIndex("wxid");
            int notenameIndex = cursor.getColumnIndex("notename");
            int nicknameIndex = cursor.getColumnIndex("nickname");
            int datetimeIndex = cursor.getColumnIndex("datetime");
            int typeIndex = cursor.getColumnIndex("type");
            int isothersidedeletedIndex = cursor.getColumnIndex("isothersidedeleted");

            int id = cursor.getInt(_idIndex); // 得到一条结果中此列的值
            String phone = cursor.getString(phoneIndex);
            String wxid = cursor.getString(wxidIndex);
            String notename = cursor.getString(notenameIndex);
            String nickname = cursor.getString(nicknameIndex);
            String datetime = cursor.getString(datetimeIndex);
            String type = cursor.getString(typeIndex);
            int isothersidedeletedInt = cursor.getInt(isothersidedeletedIndex);
            boolean isothersidedeleted = 1 == isothersidedeletedInt;

            list.add(new ModelFriend(id + "", phone, wxid, notename, nickname, datetime, type, isothersidedeleted));
        }

        cursor.close();
        rdb.close();

        UtilLogs.saveLog("SQLITE_ 查询全部朋友：" + list.size());

        return list;
    }

}
